﻿using HKQuery.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace HKQuery.DAL
{
    public partial class Query
    {

        public DataSet GetList1(string strWhere1, string strWhere2,string gp,string xm,string od,string fm, int PageIndex, int PageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"WITH t AS 
(SELECT ROW_NUMBER() OVER(ORDER BY "+od+@" ) AS row_number, "+xm+@"as Xm,
 Count(pzxh) BS,sum(jkje) je , 
 sum(case when jkdzt_dm='1' then 1 else 0 end ) BS_WJB , sum(case when jkdzt_dm='1' then jkje else 0 end ) je_WJB , 
 sum(case when jkdzt_dm='2' then 1 else 0 end ) BS_YJB , sum(case when jkdzt_dm='2' then jkje else 0 end ) je_YJB , 
 sum(case when jkdzt_dm='3' then 1 else 0 end ) BS_YZF , sum(case when jkdzt_dm='3' then jkje else 0 end ) je_YZF, 
 sum(case when JKJE_XJ<>0 then 1 else 0 end ) BS_XJ , sum(JKJE_XJ ) JE_XJ, 
 sum(case when JKJE_SK<>0 then 1 else 0 end ) BS_SK ,sum(JKJE_SK) Je_SK,  
 sum(case when JKJE_ZP<>0 then 1 else 0 end ) BS_ZP ,sum(JKJE_ZP) JE_ZP 
"+fm);
            if (strWhere1.Trim() != "")
            {
                strSql.Append( strWhere1);
            }
            if (strWhere2.Trim() != "")
            {
                strSql.Append(" where " + strWhere2);
            }
            strSql.Append(@" Group by "+gp+@"   )


SELECT (SELECT COUNT(*) from t)  AS ct , * from t where row_number BETWEEN " + ((PageIndex - 1) * PageSize + 1) + " AND " + (PageIndex * PageSize));
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList2(string strWhere1, string strWhere2, string gp, string xm, string od, string fm, int PageIndex, int PageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"WITH t AS 
(SELECT ROW_NUMBER() OVER(ORDER BY " + od + @" ) AS row_number, " + xm + @"as Xm,
 Count(pzxh) BS,sum(jkje) je , 
 sum(case when jkdzt_dm='1' then 1 else 0 end ) BS_WJB , sum(case when jkdzt_dm='1' then jkje else 0 end ) je_WJB , 
 sum(case when jkdzt_dm='2' then 1 else 0 end ) BS_YJB , sum(case when jkdzt_dm='2' then jkje else 0 end ) je_YJB , 
 sum(case when jkdzt_dm='3' then 1 else 0 end ) BS_YZF , sum(case when jkdzt_dm='3' then jkje else 0 end ) je_YZF, 
 sum(case when JKJE_XJ<>0 then 1 else 0 end ) BS_XJ , sum(JKJE_XJ ) JE_XJ, 
 sum(case when JKJE_SK<>0 then 1 else 0 end ) BS_SK ,sum(JKJE_SK) Je_SK,  
 sum(case when JKJE_ZP<>0 then 1 else 0 end ) BS_ZP ,sum(JKJE_ZP) JE_ZP 
" + fm + " WHERE   ");
            if (strWhere2.Trim() != "")
            {
                strSql.Append(strWhere2);
            }
            if (strWhere1.Trim() != "")
            {
                strSql.Append("  " + strWhere1);
            }
            strSql.Append(@" Group by " + gp + @"   )


SELECT (SELECT COUNT(*) from t)  AS ct , * from t where row_number BETWEEN " + ((PageIndex - 1) * PageSize + 1) + " AND " + (PageIndex * PageSize));
            return DbHelperSQL.Query(strSql.ToString());
        }



        public DataSet GetList1HJ(string strWhere1, string strWhere2, string gp, string xm, string od, string fm, int PageIndex, int PageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"WITH t AS 
(SELECT ROW_NUMBER() OVER(ORDER BY " + od + @" ) AS row_number, " + xm + @"as Xm,
 Count(pzxh) BS,sum(jkje) je , 
 sum(case when jkdzt_dm='1' then 1 else 0 end ) BS_WJB , sum(case when jkdzt_dm='1' then jkje else 0 end ) je_WJB , 
 sum(case when jkdzt_dm='2' then 1 else 0 end ) BS_YJB , sum(case when jkdzt_dm='2' then jkje else 0 end ) je_YJB , 
 sum(case when jkdzt_dm='3' then 1 else 0 end ) BS_YZF , sum(case when jkdzt_dm='3' then jkje else 0 end ) je_YZF, 
 sum(case when JKJE_XJ<>0 then 1 else 0 end ) BS_XJ , sum(JKJE_XJ ) JE_XJ, 
 sum(case when JKJE_SK<>0 then 1 else 0 end ) BS_SK ,sum(JKJE_SK) Je_SK,  
 sum(case when JKJE_ZP<>0 then 1 else 0 end ) BS_ZP ,sum(JKJE_ZP) JE_ZP 
" + fm);
            if (strWhere1.Trim() != "")
            {
                strSql.Append(strWhere1);
            }
            if (strWhere2.Trim() != "")
            {
                strSql.Append(" where " + strWhere2);
            }
            strSql.Append(@" Group by " + gp + @"   )

SELECT (SELECT COUNT(*) from t)  AS ct ,  
     
     SUM(    t.BS ) AS BS,
      SUM(   t.je) AS je,
      SUM(   t.BS_WJB) AS BS_WJB ,
      SUM(   t.je_WJB) AS je_WJB ,
      SUM(   t.BS_YJB ) AS BS_YJB,
      SUM(   t.je_YJB ) as je_YJB,
      SUM(   t.BS_YZF) AS BS_YZF,
      SUM(   t.je_YZF )  AS je_YZF,
       SUM(  t.BS_XJ ) AS BS_XJ,
       SUM(  t.JE_XJ ) AS JE_XJ,
        SUM( t.BS_SK ) AS BS_SK,
       SUM(  t.Je_SK ) AS Je_SK,
       SUM(  t.BS_ZP) AS BS_ZP,
       SUM(  t.JE_ZP) AS JE_ZP FROM t 


");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList2HJ(string strWhere1, string strWhere2, string gp, string xm, string od, string fm, int PageIndex, int PageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"WITH t AS 
(SELECT ROW_NUMBER() OVER(ORDER BY " + od + @" ) AS row_number, " + xm + @"as Xm,
 Count(pzxh) BS,sum(jkje) je , 
 sum(case when jkdzt_dm='1' then 1 else 0 end ) BS_WJB , sum(case when jkdzt_dm='1' then jkje else 0 end ) je_WJB , 
 sum(case when jkdzt_dm='2' then 1 else 0 end ) BS_YJB , sum(case when jkdzt_dm='2' then jkje else 0 end ) je_YJB , 
 sum(case when jkdzt_dm='3' then 1 else 0 end ) BS_YZF , sum(case when jkdzt_dm='3' then jkje else 0 end ) je_YZF, 
 sum(case when JKJE_XJ<>0 then 1 else 0 end ) BS_XJ , sum(JKJE_XJ ) JE_XJ, 
 sum(case when JKJE_SK<>0 then 1 else 0 end ) BS_SK ,sum(JKJE_SK) Je_SK,  
 sum(case when JKJE_ZP<>0 then 1 else 0 end ) BS_ZP ,sum(JKJE_ZP) JE_ZP 
" + fm + " WHERE   ");
            if (strWhere2.Trim() != "")
            {
                strSql.Append(strWhere2);
            }
            if (strWhere1.Trim() != "")
            {
                strSql.Append("  " + strWhere1);
            }
            strSql.Append(@" Group by " + gp + @"   )
SELECT (SELECT COUNT(*) from t)  AS ct ,  
     
     SUM(    t.BS ) AS BS,
      SUM(   t.je) AS je,
      SUM(   t.BS_WJB) AS BS_WJB ,
      SUM(   t.je_WJB) AS je_WJB ,
      SUM(   t.BS_YJB ) AS BS_YJB,
      SUM(   t.je_YJB ) as je_YJB,
      SUM(   t.BS_YZF) AS BS_YZF,
      SUM(   t.je_YZF )  AS je_YZF,
       SUM(  t.BS_XJ ) AS BS_XJ,
       SUM(  t.JE_XJ ) AS JE_XJ,
        SUM( t.BS_SK ) AS BS_SK,
       SUM(  t.Je_SK ) AS Je_SK,
       SUM(  t.BS_ZP) AS BS_ZP,
       SUM(  t.JE_ZP) AS JE_ZP FROM t 


"); 
            return DbHelperSQL.Query(strSql.ToString());
        }
    }
}
